﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using Mx.Ipn.Escom.TT20080015.Modelo.Datos;
using MySql.Data.MySqlClient;
using System.Web.Services;
using Mx.Ipn.Escom.TT20080015.Modelo.DB;

namespace Mx.Ipn.Escom.TT20080015.Modelo.DB
{
    public class PacienteManager : ConexionMySql
    {
        public static void Registrar(Paciente p)
        {
            MySqlCommand command = getcommand();

            command.CommandText = "CALL registrar_paciente" + p.ToString();
            try
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch(MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public static Paciente Consultar(int id)
        {
            Paciente paciente;
            MySqlCommand command = getcommand();

            command.CommandText = "CALL consultar_paciente_por_id(?value)";
            command.Parameters.Add("?value", id);
            try
            {
                command.Connection.Open();
                MySqlDataReader rd = command.ExecuteReader();
                if (rd.Read())
                {
                    paciente = new Paciente(Convert.ToInt32(rd.GetString(0)),
                        rd.GetString(1),
                        rd.GetString(2),
                        rd.GetString(3),
                        rd.GetString(4),
                        rd.GetBoolean(5),
                        rd.GetString(6),
                        rd.GetString(7),
                        rd.GetString(8),
                        rd.GetString(9),
                        rd.GetString(10));
                }
                else
                    paciente = new Paciente();
            }
            finally
            {
                command.Connection.Close();
            }

            return paciente;
        }

        public static int LastInsertedId()
        {
            int res = 0;
            MySqlCommand command = getcommand();

            command.CommandText = "SELECT LAST_INSERT_ID()";
            try
            {
                command.Connection.Open();
                res = Convert.ToInt32(command.ExecuteScalar());
            }
            finally
            {
                command.Connection.Close();
            }
            return res;
        }

        /*[WebMethod]*/
        public static DataSet GetPacientesByNombre(string nombre)
        {
            MySqlCommand command = getcommand();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "CALL consultar_pacientes_por_nombre(?value)";
            command.Parameters.Add("?value", nombre);
            try
            {
                command.Connection.Open();
                command.Prepare();
                command.ExecuteScalar();
            }
            finally
            {
                command.Connection.Close();
            }

            MySqlDataAdapter da = new MySqlDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds, "pacientes");
            da.Fill(ds);

            return ds;
        }

        public static void Actualizar(Paciente p)
        {
            MySqlCommand command = getcommand();

            command.CommandText = "CALL modificar_paciente" + p.ToString();
            try
            {
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch
            {
                command.Connection.Close();
            }
        }

        public static void Eliminar(string id)
        {
            MySqlCommand command = getcommand();

            command.CommandText = "CALL eliminar_paciente(?value)";
            command.Parameters.Add("?value", id);
            try
            {
                command.Connection.Open();
                command.Prepare();
                command.ExecuteScalar();
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
}
